<?php

class sqlcon
{
	private $con = '';
	private $sql = '';
	private $result = '';
	private $row = '';

	private $serverName = _MYSQL_IP_;
	private $dataBase = _MYSQL_DB_;
	private $userName = _MYSQL_USER_;
	private $password = _MYSQL_PWD_;

	private $fieldName = array();
	private $fieldValue = array();

	public function __construct() {
		$this->con = mysqli_connect($this->serverName, $this->userName, $this->password, $this->dataBase);
		if (!$this->con) {
			die('连接失败:' . mysqli_connect_error());
		}
	}

	public function __destruct() {
		mysqli_close($this->con);
	}

	public function setSql($val) {
		$this->sql = $val;
	}

	public function getSql() {
		return $this->sql;
	}

	public function setField($val) {
		$this->sql .= 'select ' . $val;
	}

	public function setTable($val) {
		$this->sql .= ' from ' . $val;
	}

	public function setWhere($val) {
		$this->sql .= ' where ' . $val;
	}

	public function setOrder($val) {
		$this->sql .= ' order by ' . $val;
	}

	public function setGroup($val) {
		$this->sql .= ' group by ' . $val;
	}

	public function setLimit($val) {
		$this->sql .= ' limit ' . $val;
	}

	public function execute() {
		mysqli_query($this->con, 'set names "utf8"');
		mysqli_query($this->con, $this->sql);
		$this->close();
	}

	public function open() {
		mysqli_query($this->con, 'set names "utf8"');
		$this->result = mysqli_query($this->con, $this->sql);
	}

	public function isEmpty() {
		$this->row = mysqli_fetch_array($this->result);
		return $this->row;
	}

	public function getValue($val) {
		return $this->row[$val];
	}

	public function close() {
		$this->sql = '';
	}

	public function addCollect($field, $value) {
		$this->fieldName[] = $field;
		$this->fieldValue[] = $value;
	}

	public function clearCollect() {
		unset($this->fieldName);
		unset($this->fieldValue);
		$this->fieldName = array();
		$this->fieldValue = array();
	}

	public function insertInfo($tableName) {
		$count = count($this->fieldName);

		$this->sql = 'insert into ' . $tableName . '(';
		for ($i = 0; $i < $count; $i++) {
			if ($i == $count - 1)
				$this->sql .= $this->fieldName[$i] . ')';
			else
				$this->sql .= $this->fieldName[$i] . ', ';
		}

		$this->sql .= " values('";
		for ($i = 0; $i < $count; $i++) {
			if ($i != $count - 1)
                $this->sql .= $this->fieldValue[$i] . "','";
            else
                $this->sql .= $this->fieldValue[$i] . "')";
		}
		$this->execute();
		$this->clearCollect();
	}

	public function updateInfo($tableName) {
		$count = count($this->fieldName);
		
        $this->sql = 'update ' . $tableName . ' set ';
        for ($i = 0; $i < $count; $i++) {
            if ($i == $count - 1)
                $this->sql .= ' where ' . $this->fieldName[$i] . " = '" . $this->fieldValue[$i] . "'";
            else if ($i ==  $count - 2)
                $this->sql .= $this->fieldName[$i] . " = '" . $this->fieldValue[$i] . "' ";
            else
                $this->sql .= $this->fieldName[$i] . " = '" . $this->fieldValue[$i] . "', ";
        }
        $this->execute();
        $this->clearCollect();
	}

	public function deleteInfo($tableName) {
		$count = count($this->fieldName);
		
		$this->sql = 'delete from ' . $tableName . ' where ';
        for ($i = 0; $i < $count; $i++)
        {
            if ($i == 0)
                $this->sql .= $this->fieldName[$i] . " = '" . $this->fieldValue[$i] . "'";
            else
                $this->sql .= ' and ' . $this->fieldName[$i] . " = '" . $this->fieldValue[$i] . "'";
        }
        $this->execute();
        $this->clearCollect();
	}

	private function getFieldName($table) {
		$this->setSql('select a.column_name from information_schema.columns a where a.table_name = "' . $table . '"');
		$result = '';

		$this->open();
		while ($this->isEmpty()) {
			if (empty($result)) {
				$result .= $this->getValue('column_name');
			}
			else {
				$result .= ', ' . $this->getValue('column_name');
			}
		}
		$this->close();

		return $result;
	}

	public function getInfo($field, $table, $where) {
		$result = array();

		if ($field == '*') {
			$field = $this->getFieldName($table);
		}

		$this->setField($field);
		$this->setTable($table);
		$this->setWhere($where);

		$field = str_replace(', ', ',', $field);
		$field = explode(',', $field);
		$count = count($field);

		for ($i = 0; $i < $count; $i++) {
			if (strpos($field[$i], ' as ') !== false) {
				$field[$i] = explode(' as ', $field[$i])[1];
			}
			if (strpos($field[$i], '.') !== false) {
				$field[$i] = explode('.', $field[$i])[1];
			}
		}
		
		$this->open();
		if ($this->isEmpty()) {
			for ($i = 0; $i < $count; $i++) {
				$result[$field[$i]] = $this->getValue($field[$i]);
			}
		}
		$this->close();

		return empty(count($result)) ? null : $result;
	}

	public function getList($field, $table, $where, $order = null, $group = null, $limit = null) {
		$result = array();

		$this->setField($field);
		$this->setTable($table);
		$this->setWhere($where);
		if (!empty($group))
			$this->setGroup($group);
		if (!empty($order))
			$this->setOrder($order);
		if (!empty($limit))
			$this->setLimit($limit);

		$field = str_replace('distinct ', '', $field);
		$field = str_replace(', ', ',', $field);
		$field = explode(',', $field);
		$count = count($field);

		for ($i = 0; $i < $count; $i++) {
			if (strpos($field[$i], ' as ') !== false) {
				$field[$i] = explode(' as ', $field[$i])[1];
			}
			if (strpos($field[$i], '.') !== false) {
				$field[$i] = explode('.', $field[$i])[1];
			}
		}

		$this->open();
		while ($this->isEmpty()) {
			$info = array();

			for ($i = 0; $i < $count; $i++) {
				$info[$field[$i]] = $this->getValue($field[$i]);
			}

			$result[] = $info;
		}
		$this->close();

		return $result;
	}
}